import { Inject, Provide } from '@midwayjs/core';
import { In, Repository } from 'typeorm';
import { Student } from '../../entity/firmware/student.entity';
import { ClassEntity } from '../../entity/firmware/class.entity';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { ImportExcelDTO } from '@dto/common/excel.dto';
import { ResolveExcelService } from '@service/common/resolveWebExcel';
import { resBuild } from '@utils/resBuild';
import { DownloadExcelService } from '@service/common/downloadExcel';
import { EdgroupService } from './edgroup.service';
import { SchoolService } from './school.service';
import { ClassService } from './class.service';
import { StudentWristband } from '@entity/firmware/student_wristband.entity';

@Provide()
export class StudentService {
  @InjectEntityModel(Student)
  studentRepo: Repository<Student>;
  @InjectEntityModel(StudentWristband)
  studentWristbandRepo: Repository<StudentWristband>;

  @Inject()
  protected downloadExcelService: DownloadExcelService;

  @Inject()
  protected resolveExcelService: ResolveExcelService;
  @Inject()
  protected edgroupService: EdgroupService;
  @Inject()
  protected schoolService: SchoolService;
  @Inject()
  protected classService: ClassService;

  @InjectEntityModel(ClassEntity)
  classRepo: Repository<ClassEntity>;
  ctx: any;

  // 创建学生
  async createStudent(body) {
    console.log('body', body)
    // 校验班级是否存在
    const classEntity = await this.classRepo.findOneBy({ id: body.class.id });
    if (!classEntity) throw new Error('班级不存在');

    // 校验学生名称是否有效
    if (!body.name || body.name.trim() === '') throw new Error('学生姓名不能为空');

    const student = new Student();
    student.name = body.name.trim(); // 去除多余空格
    student.s_number = body.s_number.trim();
    student.class = classEntity;
    try {
      return await this.studentRepo.save(student);
    } catch (error) {
      throw new Error(`创建学生失败: ${error.message}`);
    }
  }

  // 获取所有学生
  async getAllStudents(params) {
    try {
      const { edgroup_id, class_id, school_id, version, name, pageNum, pageSize } = params
      console.log('params', params)
      const query = this.studentRepo.createQueryBuilder('student')
        .leftJoinAndSelect('student.class', 'class') // 关联 class
        .leftJoinAndSelect('class.school', 'school')
        .leftJoinAndSelect('school.edgroup', 'edgroup')
        .leftJoinAndSelect('student.student_wristband', 'sw') // 关联学生手环表
        .leftJoinAndSelect('sw.wristband', 'wristband') // 关联手环表
        .where('school.deleted != :deleted', { deleted: true });
        console.log('name1', name)
      if (name) {
        query.andWhere('student.name LIKE :name', { name: `%${name}%` });
      }
      // edgroup_id
      if (edgroup_id) {
        query.andWhere('school.edgrp_id = :edgrp_id', { edgrp_id: edgroup_id });
      }
      if (school_id) {
        query.andWhere('class.school_id = :school_id', { school_id: school_id });
      }
      if (class_id) {
        query.andWhere('class.id = :class_id', { class_id: class_id });
      }
      if (version) {
        query.andWhere('wristband.fw_version LIKE :version', { version: `%${version}%` });
      }

      if (pageNum && pageSize) {
        // 添加分页查询
        query.skip((pageNum - 1) * pageSize).take(pageSize);
      }


      // // 输出生成的 SQL 查询和参数
      // const [sql, parameters] = query.getQueryAndParameters();
      // console.log('Generated SQL:', sql);
      // console.log('Parameters:', parameters);
      // 查询总数 count
      const total = await query.getCount();
      return {
        // getRawMany() 是使结果扁平化
        data: await query.getMany(),
        total: total
      }
    } catch (error) {
      throw new Error(`获取学生列表失败: ${error.message}`);
    }
  }


  // 下载导入模板
  async importTemplate() {
    console.log('importTemplate')

    // 1. 获取字典数据（集团、学校、班级）
    const edgroups = await this.edgroupService.getAllEdgroups({});
    const schools = await this.schoolService.getAllSchools({});
    const classes = await this.classService.getAllClasses({});

    // 2. 定义表头
    const headers = [
      // { label: "集团名称", prop: "edgroup" },
      // { label: "学校名称", prop: "school" },
      { label: "班级名称", prop: "class" },
      { label: "学生名称", prop: "name" },
      { label: "学号", prop: "s_number" },
    ];

    // 3. 定义动态下拉选项配置
    const dropdownOptions = {
      edgroup: {
        data: edgroups.data.map(eg => eg.name), // 集团名称下拉选项
      },
      school: {
        data: schools.data.map(s => s.name), // 学校名称下拉选项
        dependency: {
          column: 'edgroup', // 依赖的列（集团名称）
          mapping: schools.data.reduce((map, school) => {
            const edgroupName = edgroups.data.find(eg => eg.id === school.edgroup.id)?.name;
            if (edgroupName) {
              if (!map[edgroupName]) map[edgroupName] = [];
              map[edgroupName].push(school.name);
            }
            return map;
          }, {}),
        },
      },
      class: {
        data: classes.data.map(c => c.name), // 班级名称下拉选项
        dependency: {
          column: 'school', // 依赖的列（学校名称）
          mapping: classes.data.reduce((map, cls) => {
            const schoolName = schools.data.find(s => s.id === cls.school.id)?.name;
            if (schoolName) {
              if (!map[schoolName]) map[schoolName] = [];
              map[schoolName].push(cls.name);
            }
            return map;
          }, {}),
        },
      },
    };
    console.log('dropdownOptions', dropdownOptions);

    // 4. 生成 Excel 模板
    return this.downloadExcelService.downloadExcel({
      headers: headers,
      data: [], // 导入模板数据为空
      sheetName: '学生导入模板',
      dropdownOptions, // 传入动态下拉选项配置
    });
  }


  // 下载导入模板
  async importTemplateClass() {
    console.log('importTemplateClass')

    // 1. 获取字典数据（集团、学校、班级）
    const edgroups = await this.edgroupService.getAllEdgroups({});
    const schools = await this.schoolService.getAllSchools({});
    const classes = await this.classService.getAllClasses({});

    // 2. 定义表头
    const headers = [
      // { label: "集团名称", prop: "edgroup" },
      // { label: "学校名称", prop: "school" },
      // { label: "班级名称", prop: "class" },
      { label: "学生名称", prop: "name" },
      { label: "学号", prop: "s_number" },
    ];

    // 3. 定义动态下拉选项配置
    const dropdownOptions = {
      edgroup: {
        data: edgroups.data.map(eg => eg.name), // 集团名称下拉选项
      },
      school: {
        data: schools.data.map(s => s.name), // 学校名称下拉选项
        dependency: {
          column: 'edgroup', // 依赖的列（集团名称）
          mapping: schools.data.reduce((map, school) => {
            const edgroupName = edgroups.data.find(eg => eg.id === school.edgroup.id)?.name;
            if (edgroupName) {
              if (!map[edgroupName]) map[edgroupName] = [];
              map[edgroupName].push(school.name);
            }
            return map;
          }, {}),
        },
      },
      class: {
        data: classes.data.map(c => c.name), // 班级名称下拉选项
        dependency: {
          column: 'school', // 依赖的列（学校名称）
          mapping: classes.data.reduce((map, cls) => {
            const schoolName = schools.data.find(s => s.id === cls.school.id)?.name;
            if (schoolName) {
              if (!map[schoolName]) map[schoolName] = [];
              map[schoolName].push(cls.name);
            }
            return map;
          }, {}),
        },
      },
    };
    console.log('dropdownOptions', dropdownOptions);

    // 4. 生成 Excel 模板
    return this.downloadExcelService.downloadExcel({
      headers: headers,
      data: [], // 导入模板数据为空
      sheetName: '学生导入模板',
      dropdownOptions, // 传入动态下拉选项配置
    });
  }

  // 导入 下载的模板(携带 class_id 参数)
  async importDataClass(body: { class_id: number } & ImportExcelDTO) {
    const { files, updateSupport, class_id } = body; // 从body中获取class_id
    // 1.先解析模板
    const result = await this.resolveExcelService.resolvedWebExcelStream(files);

    // 2.获取模板的数据
    const { tableData } = result;
    console.log('tableData', tableData)

    // 3. 验证班级是否存在（重要！）
    const classExists = await this.classRepo.findOne({ where: { id: class_id } });
    if (!classExists) {
      throw new Error(`班级ID ${class_id} 不存在`);
    }


    // 4.处理表格数据，添加class_id
    const processedData = tableData.map(row => {
      // 将数组格式转换为对象格式
      return {
        name
          : row[0],       // 第一列是姓名
        s_number
          : row[1],   // 第二列是学号
        class: { id: class_id }, // 转换为关系对象
      };
    });
    console.log('processedData', processedData)


    // 检查同一个班级内学号是否重复
    const sNumbersInClass = processedData.map(item => item.s_number);
    const uniqueSNumbers = [...new Set(sNumbersInClass)];
    if (sNumbersInClass.length !== uniqueSNumbers.length) {
      throw new Error('同一个班级内存在重复的学号，请检查数据');
    }


    // 检查数据库中是否已存在这些学号（同一个班级内）
    const queryBuilder = this.studentRepo.createQueryBuilder('student')
      .leftJoinAndSelect('student.class', 'class')
      .where('class.id = :class_id', { class_id })
      .andWhere('student.s_number IN (:...s_numbers)', { s_numbers: sNumbersInClass });
    const existingStudents = await queryBuilder.getMany();
    console.log('existingStudents', existingStudents)
    // 如果存在重复的学号，且没有启用更新选项，则抛出错误
    // 这里的 updateSupport 是一个布尔值，表示是否启用更新选项
    // 如果启用更新选项，则允许重复的学号存在
    if (existingStudents.length > 0 && !updateSupport) {
      const duplicateNumbers = existingStudents.map(s => s.s_number);
      throw new Error(`班级 ${class_id} 中以下学号已存在: ${duplicateNumbers.join(', ')}。如需更新，请启用更新选项`);
    }

    try {
      // 5.循环校验数据，校验通过就插入数据库，校验失败就记录下来，最后返回给前端 校验并写入数据
      const tipMsg = await this.resolveExcelService.analysisExcelAndWhite({
        tableData: processedData,
        updateSupport,
        entityName: this.studentRepo,
        headerFields: ['name', 's_number', 'class.id'], // 确保字段名匹配
        uniqueKey: 's_number',
        // 添加额外的校验条件，确保只在同一个班级内检查学号唯一性
        additionalCondition: { class: { id: class_id } },
      });
      return resBuild.success(tipMsg);
    } catch (err) {
      console.log('err0', err)
      return resBuild.fail(err);
    }


  }

  // 根据 ID 获取单个学生
  async getStudentById(id: number) {
    try {
      const student = await this.studentRepo.findOne({
        where: { id },
        relations: ['class', 'class.school', 'class.school.edgroup'],
      });

      if (!student) {
        throw new Error('学生不存在');
      }

      return student;
    } catch (error) {
      throw new Error(`获取学生信息失败: ${error.message}`);
    }
  }

  // 更新学生信息
  async updateStudent(params) {
    try {
      const student = await this.getStudentById(params.id); // 检查学生是否存在
      if (!student) return null; // 如果学生不存在，返回 null

      // 更新学生名称
      if (params.name !== undefined && params.name.trim() !== '') student.name = params.name.trim();

      // 更新学生班级
      if (params.class_id !== undefined) {
        const classEntity = await this.classRepo.findOneBy({ id: params.class_id });
        if (!classEntity) {
          throw new Error('班级不存在');
        }
        student.class = classEntity;
      }

      return await this.studentRepo.save(params);
    } catch (error) {
      throw new Error(`更新学生失败: ${error.message}`);
    }
  }

  // 删除学生
  async deleteStudent(id: number) {
    try {
      console.log('id', id)
      const student = await this.getStudentById(id); // 检查学生是否存在
      if (!student) return false; // 如果学生不存在，返回 false
      // 先删除关联的手环记录
      await this.studentWristbandRepo.delete({ sid: id });
      const result = await this.studentRepo.delete(id);
      return result.affected > 0; // 判断是否成功删除
    } catch (error) {
      throw new Error(`删除学生失败: ${error.message}`);
    }
  }


  // 批量删除选中的学生
  async deleteSelectedStudents(ids: number[]) {
    try {
      // 校验班级是否存在
      let deletedCount = 0;
      // 使用事务确保所有删除操作都成功
      await this.studentRepo.manager.transaction(async transactionalEntityManager => {
        // 先删除关联的手环记录
        await transactionalEntityManager.delete(StudentWristband, { sid: In(ids) });
        // 然后删除学生记录
        const result = await transactionalEntityManager.delete(Student, ids);
        deletedCount
          = result.affected || 0;
      });
      return deletedCount;
    } catch (error) {
      throw new Error(`批量删除学生失败: ${error.message}`);
    }
  }

  // 根据 class_id 删除所有学生
  async deleteAllStudentsByClass(class_id: number) {
    try {
      // 校验班级是否存在
      const classEntity = await this.classRepo.findOneBy({ id: class_id });
      if (!classEntity) throw new Error('班级不存在');
      let deletedCount = 0;

      // 先查询该班级的所有学生ID
      const students = await this.studentRepo.find({
        where
          : { class: { id: class_id } },
        select
          : ['id']
      });
      if (students.length === 0) {
        return 0;
      }
      const studentIds = students.map(student => student.id);

      // 使用事务确保所有删除操作都成功
      await this.studentRepo.manager.transaction(async transactionalEntityManager => {
        // 先删除关联的手环记录
        await transactionalEntityManager.delete(StudentWristband, { sid: In(studentIds) });
        // 然后删除学生记录
        const result = await transactionalEntityManager.delete(Student, { class: { id: class_id } }); // 使用关系删除
        deletedCount = result.affected || 0;
      });

      return deletedCount;
    } catch (error) {
      throw new Error(`清空班级学生失败: ${error.message}`);
    }
  }
}
